# _*_coding:utf-8 _*_
# @Time　　:2021/1/31 15:28
# @Author　 : wangyong
# @File　　  :do_excel.py
# @Software  :PyCharm
import openpyxl

from ApiTest.utils.log import get_log

log = get_log("Excel")


class DoExcel:
    fileName = None
    wk = None

    def __init__(self, fileName):
        try:
            # 需要打开的文件
            self.fileName = fileName

            # 实例化workbook对象
            if self.wk is None:
                self.wk = openpyxl.load_workbook(filename=fileName)
        except Exception as e:
            print(e)

    def read_excel(self, sheetName):
        # 获取sheet页面
        sheet = self.wk[sheetName]
        # 获取最大行数
        max_row = sheet.max_row
        cases = []
        # 根据循环获取某一行数据 存到cases 列表并返回
        for i in range(2, max_row+1):
            case = {}
            case["case_id"] = sheet.cell(row=i, column=1).value
            case["title"] = sheet.cell(row=i, column=2).value
            case["method"] = sheet.cell(row=i, column=3).value
            case["url"] = sheet.cell(row=i, column=4).value
            case["headers"] = sheet.cell(row=i, column=5).value
            case["data"] = sheet.cell(row=i, column=6).value
            case["expected"] = sheet.cell(row=i, column=7).value
            cases.append(case)

        return cases

    def write_excel(self, sheetName, row, actual, result):
        # 获取sheet页面
        sheet = self.wk[sheetName]
        sheet.cell(row=row, column=8).value = actual
        sheet.cell(row=row, column=9).value = result

    def save_excel(self):
        self.wk.save(filename=self.fileName)
